06 바인드 변수의 부작용과 해법

SQL> CREATE TABLE t
  2  AS
  3  SELECT ROWNUM no
  4    FROM dual
  5  CONNECT BY LEVEL <= 1000
  6  ;

테이블이 생성되었습니다.

SQL> ANALYZE TABLE t COMPUTE STATISTICS FOR TABLE FOR ALL COLUMNS;

테이블이 분석되었습니다.

SQL> EXPLAIN PLAN FOR SELECT * FROM t WHERE no <= :no;

해석되었습니다.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null, null, 'basic rows'));

Plan hash value: 1601196873

------------------------------------------
| Id  | Operation         | Name | Rows  |
------------------------------------------
|   0 | SELECT STATEMENT  |      |    50 |
|   1 |  TABLE ACCESS FULL| T    |    50 |
------------------------------------------

8 개의 행이 선택되었습니다.

SQL> EXPLAIN PLAN FOR SELECT * FROM t WHERE no BETWEEN :no1 AND :no2;

해석되었습니다.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null, null, 'basic rows'));

Plan hash value: 1322348184

-------------------------------------------
| Id  | Operation          | Name | Rows  |
-------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |
|   1 |  FILTER            |      |       |
|   2 |   TABLE ACCESS FULL| T    |     3 |
-------------------------------------------

9 개의 행이 선택되었습니다.

SQL> EXPLAIN PLAN FOR SELECT * FROM t WHERE no <= 100;

해석되었습니다.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null, null, 'basic rows'));

Plan hash value: 1601196873

------------------------------------------
| Id  | Operation         | Name | Rows  |
------------------------------------------
|   0 | SELECT STATEMENT  |      |    99 |
|   1 |  TABLE ACCESS FULL| T    |    99 |
------------------------------------------

8 개의 행이 선택되었습니다.

SQL> EXPLAIN PLAN FOR SELECT * FROM t WHERE no BETWEEN 500 AND 600;

해석되었습니다.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null, null, 'basic rows'));

Plan hash value: 1601196873

------------------------------------------
| Id  | Operation         | Name | Rows  |
------------------------------------------
|   0 | SELECT STATEMENT  |      |    99 |
|   1 |  TABLE ACCESS FULL| T    |    99 |
------------------------------------------

8 개의 행이 선택되었습니다.

• 바인드 변수를 사용하면 최적이 아닌 실행계획을 수립할 가능성이 높다.
  1. 바인드 변수를 사용할때 정확한 컬럼히스토그램에 근거하지 않고 정해진 계산식에 기초해 비용을 계산
  2. 파티션 테이블 쿼리 시 파티션 레벨 통계정보를 이용하지 못함, 테이블 레벨 통계정보 이용

(1) 바인드 변수 Peeking

• 바인드 변수 Peeking
  1. 바인드 변수의 부작용을 극복하기 위해 9i 부터 peeking 기능 도입
  2. Sql의 첫번째 수행시 입력된 바인딩값을 살짝 훔쳐보고 그값에 대한 분포도를 이용해 실행계획 수립
  3. SQL Server 의 Parameter Sniffing

• 바인드 변수 Peeking의 부작용
  • 예) 아파트매물과 같이 분포도가 고르지 못한 자료 조회
    
    SELECT * FROM 아파트매물 WHERE 도시 = :City;
  1. 최초 Sql 실행시 '서울'로 조회하면
     => 넓은 범위를 인덱스 스캔하는 것보다 풀스캔이 유리하므로 풀스캔으로 실행계획을 세움
     => 이후 어떤 값이 들어오든지 무조건 풀스캔으로 실행계획을 공유하게 됨
     => 제주도나 강원도는 인덱스 스캔을 통해 빠른 성능을 발휘할 수 있음에도 풀스캔을 하게 됨
  2. 최초 Sql 실행시 '강원도'로 조회하면
     => 인덱스 스캔으로 실행계획을 세움
     => 이후 어떤 값이 들어오든지 무조건 인덱스 스캔으로 실행계획을 공유하게 됨
     => 서울 조회시 인덱스 스캔으로 넓은 범위를 조회하게 되어 성능 저하 발생

• 10G에서의 Peeking
  1. 10G부터는 dbms_stats의 기본설정이 히스토그램을 설정할지 여부를 오라클이 판단하게끔 바뀌었다.
  2. 이전에 히스토그램이 있는지도 모르고 사용을 안해왔던 사용자들이 대부분
  3. 컬럼에 대한 히스토그램이 더 많이 생성되면서 Peeking의 부작용이 더 심각하게 나타나게 된다.
  4. 이로 인해 10g에서는 바인드 변수를 쓰지 말고 리터럴 상수로 회귀하자는 움직임도 나타남.
  5. Peeking 기능 비 활성화
     ALTER SYSTEM SET "_optim_peek_user_binds" = FALSE;
  6. EXPLAIN PLAN 을 통해 확인하는 사전 실행계획은 바인드 변수값이 주어지지 않은 상태에서의 평균분포도 실행계획
     실제 수행시 변수값이 바인딩 되면서 Peeking 기능으로 인해 확인했던 실행계획과 다른 실행계획으로 풀릴 수 있다.

(2) 적응적 커서 공유(Adaptive Cursor Sharing)

• 10g에서 Peeking의 부작을을 해소하기 위해 11g에서 도입된 기능

• 적응적 커서 공유 수행 매카니즘
  1. 기본적으로 이기능이 사용되려면 컬럼에 대한 히스토그램이 생성되어 있어야 한다.
  2. 옵티마이져가 바인드변수값에 따라 실행계획이 달라져야 한다고 판단
  3. 이러한 커서를 Bind Sensitive 커서라고 부른다.
  4. '서울시' 조회시 풀스캔 실행계획으로 커서 캐시(이를 1번커서라 칭하자)
  5. '제주도' 조회시 1번커서를 그대로 사용(비효율 발생)
  6. 이전에 비해 많은 일량을 처리한 것으로 판단
  7. 이커서의 모드를 Bind Aware 모드로 전환, 커서 공유 불가
  8. 다시 '제주도' 조회시 인덱스스캔용 새 커서를 만든다(2번 커서)
  9. 만약 이때 '제주도'가 아닌 '경기도'가 입력된다면
  10. 이때도 공유커서가 없으므로 새로운 실행계획 수립
  11. 다만 이때 만들어진 커서는 Bind Aware 모드로 있던 커서와 동일하므로
      그중 하나만 사용하고 나머지는 버린다.(같은 커서가 불필요하게 많이 만들어지는것을 방지)
  12. 결국, 입력되는 자료에 따라 그때 그때 적절한 커서를 공유하는 것은 아니다.
      비효율이 한번 발생한후 커서 공유 중단하고, 그 다음 실행시 다시 실행계획을 수립하는 방식

• 적응적 커서 공유 확인 뷰
  1. v$sql_cs_statistics
  2. v$sql_cs_histogram
  3. v$sql_cs_selectivity

(3) 입력값에 따라 SQL 분리

• 바인드 변수의 부작용을 해소하기 위한 DBMS의 노력 VS 개발자의 노력
• Sql 분리 - Union all
  
  
  

SELECT /*+ FULL(a) */ *
  FROM 아파트매물 a
 WHERE :City IN ('서울시', '경기도')
   AND 도시 = :City
 UNION ALL
SELECT /*+ INDEX(a idx01) */ *
  FROM 아파트매물 a
 WHERE :City NOT IN ('서울시', '경기도')
   AND 도시 = :City
;